use core::fmt::Debug;
use once_cell::sync::OnceCell;
use rust_decimal::Decimal;
use serde::Serialize;
use serde::de::DeserializeOwned;
use serde_json::{Map, Value, json};
use sqlx::Transaction;
use sqlx::mysql::{MySqlPoolOptions, MySqlRow};
use sqlx::{Column, MySql, Pool, Row, TypeInfo};

use super::my_err::MyErr;
use super::*;
//use crate::api::MemberBean;

pub static DB: OnceCell<Pool<MySql>> = OnceCell::new();

pub trait UserTrait {
    fn get_name(&self) -> String;
    fn get_id(&self) -> i64;
}

pub struct SystemUser {}

impl UserTrait for SystemUser {
    fn get_name(&self) -> String {
        return "System".to_string();
    }

    fn get_id(&self) -> i64 {
        return 9999999;
    }
}

pub async fn init_db(db_url: &str, connect_max: u32) {
    //let db_url = "mysql://laobudb:Laobu%23851124@rm-cn-4xl3k22il000h4xo.rwlb.rds.aliyuncs.com:3306/car_record";
    //let db_url = &CONFIG.database.url;
    let pool = MySqlPoolOptions::new().max_connections(connect_max).connect(db_url).await.unwrap();
    let _ = DB.set(pool);
    log::info!("初始化数据库");
}

pub fn get_pool() -> Result<&'static Pool<MySql>, MyErr> {
    if let Some(pool) = DB.get() {
        Ok(pool)
    } else {
        Err(MyErr::Msg("无法连接至数据库".to_string()))
    }
}

/// 得到事务处理的连接
pub async fn get_transaction() -> Result<Transaction<'static, MySql>, MyErr> {
    let pool = get_pool()?;
    let tx = pool.begin().await?;
    return Ok(tx);
}

#[derive(Clone, PartialEq)]
#[allow(dead_code)]
enum MyValue {
    Usize(usize),
    I32(i32),
    I64(i64),
    U32(u32),
    U64(u64),
    F32(f32),
    F64(f64),
    String(String),
}

impl MyValue {
    #[inline]
    pub fn is_integer(&self) -> bool {
        match *self {
            MyValue::Usize(..) | MyValue::I32(..) | MyValue::I64(..) | MyValue::U32(..) | MyValue::U64(..) => true,
            _ => false,
        }
    }

    #[inline]
    pub fn is_float(&self) -> bool {
        match *self {
            MyValue::F32(..) | MyValue::F64(..) => true,
            _ => false,
        }
    }

    #[inline]
    pub fn is_str(&self) -> bool {
        self.as_str().is_some()
    }

    #[inline]
    pub fn as_i64(&self) -> Option<i64> {
        match *self {
            MyValue::Usize(ref n) => Some(n.to_owned() as i64),
            MyValue::I32(ref n) => Some(n.to_owned() as i64),
            MyValue::I64(ref n) => Some(n.to_owned() as i64),
            MyValue::U32(ref n) => Some(n.to_owned() as i64),
            MyValue::U64(ref n) => Some(n.to_owned() as i64),
            _ => None,
        }
    }

    #[inline]
    pub fn as_float(&self) -> Option<f64> {
        match *self {
            MyValue::F32(ref n) => Some(n.to_owned() as f64),
            MyValue::F64(ref n) => Some(n.to_owned() as f64),
            _ => None,
        }
    }

    #[inline]
    pub fn as_str(&self) -> Option<String> {
        match self {
            MyValue::String(s) => Some(s.to_string()),
            _ => None,
        }
    }
}

// 设置 debug 输出格式，便于控制显示内容
impl std::fmt::Debug for MyValue {
    fn fmt(&self, f: &mut std::fmt::Formatter) -> std::fmt::Result {
        let print_str = match *self {
            MyValue::Usize(ref n) => write!(f, "{}", n),
            MyValue::I32(ref n) => write!(f, "{}", n),
            MyValue::I64(ref n) => write!(f, "{}", n),
            MyValue::U32(ref n) => write!(f, "{}", n),
            MyValue::U64(ref n) => write!(f, "{}", n),
            MyValue::F32(ref n) => write!(f, "{}", n),
            MyValue::F64(ref n) => write!(f, "{}", n),
            MyValue::String(ref n) => write!(f, "'{}'", n),
        };
        print_str
    }
}

pub struct Mdb {
    table: String,           // 查询的表名，如 “sys_orgin”
    field: String,           // 查询字段,如 “id， name， idcard”， 默认：“*”
    where_str: String,       // 查询语句中的 where 字符串，如：“id = ? and sort = ?”
    save_str: String,        // 修改语句（update，insert）中的更新内容语句, 如：“ext=?, name=?”
    save_log_str: String,    // 用于保存的操作记录语句，如："select * from sys_organ where id = ?, [1]"
    order_by: String,        // 排序语句, 如："id desc"
    limit: String,           // 分页语句，如："0, 10"
    where_var: Vec<MyValue>, // 查询语句对应的参数
    save_var: Vec<MyValue>,  // 更新语句对应的参数
    print_log: bool,         // 是否打印sql语句
}

impl Clone for Mdb {
    fn clone(&self) -> Self {
        Self {
            table: self.table.clone(),
            field: self.field.clone(),
            where_str: self.where_str.clone(),
            save_str: self.save_str.clone(),
            save_log_str: self.save_log_str.clone(),
            order_by: self.order_by.clone(),
            limit: self.limit.clone(),
            where_var: self.where_var.clone(),
            save_var: self.save_var.clone(),
            print_log: self.print_log.clone(),
        }
    }
}

impl Mdb {
    pub fn table(table_name: &str) -> Self {
        Mdb {
            table: table_name.to_string(),
            field: "*".to_string(),
            where_str: "".to_string(),
            save_str: "".to_string(),
            save_log_str: "".to_string(),
            order_by: "".to_string(),
            limit: "".to_string(),
            where_var: Vec::new(),
            save_var: Vec::new(),
            print_log: true,
        }
    }

    /// 设置需要查询的字段，默认查询所有
    #[allow(dead_code)]
    pub fn field(mut self, fields: &str) -> Self {
        self.field = fields.to_string();
        self
    }

    ///  设置排序条件
    #[allow(dead_code)]
    pub fn order_by(mut self, order_by: &str) -> Self {
        self.order_by = order_by.to_string();
        self
    }

    /// 设置获取条数
    #[allow(dead_code)]
    pub fn limit(mut self, offset: i32, len: i32) -> Self {
        self.limit = format!("{}, {}", offset, len);
        self
    }

    /// 通过条件语句及参数添加查询条件
    /// ```
    /// // select * from sys_organ where id = 4
    /// let rows1 = Mdb::table("sys_organ")
    ///     .where_add_id(4)
    ///     .one::<SysOrgan>().await?;
    /// ```
    ///
    pub fn where_add_id(mut self, id: i64) -> Self {
        if !util_string::is_empty(&self.where_str) {
            self.where_str.push_str(" and ");
        }
        self.where_str.push_str("id = ?");
        self.where_var.push(MyValue::I64(id));

        self
    }

    /// 通过条件语句及参数添加查询条件
    /// ```
    /// let rows1 = Mdb::table("sys_organ")
    ///     .where_add_str("name = ? and name_py = ? ", json!(["中国", "zhongguo"]))
    ///     .where_add_str("password like ? and status = ?", json!(["%9845%", 1]))
    ///     .where_add_str("id in (1,2,3,4)", json!([]))
    ///     .where_add_str("sort = ? ", json!([8]))
    ///     .all::<SysOrgan>().await?;
    /// ```
    ///
    #[allow(dead_code)]
    pub fn where_add_str(mut self, where_str: &str, arry: Value) -> Self {
        if !util_string::is_empty(where_str) {
            if !util_string::is_empty(&self.where_str) {
                self.where_str.push_str(" and ");
            }
            self.where_str.push_str(where_str);

            if let Some(items) = arry.as_array() {
                for item in items {
                    if item.is_i64() {
                        self.where_var.push(MyValue::I64(item.as_i64().unwrap_or_default()));
                    } else if item.is_f64() {
                        self.where_var.push(MyValue::F64(item.as_f64().unwrap_or(0.0)));
                    } else if item.is_string() {
                        self.where_var.push(MyValue::String(item.as_str().unwrap_or_default().to_string()));
                    }
                }
            }
        }

        self
    }

    /// 通过 value （serde_json） 添加查询条件
    /// ```
    /// let val3 = json!({
    ///     "name = ?" : "李苟弚",
    ///     "name_py = ?" : "lgc",
    ///     "password like ?" : "%9845%",
    ///     "status = ?" : 1,
    ///     "id in (?)" : [3,2,1,4],
    ///     "pid between ? and ?" : [1,4],
    ///});
    ///let rows2 = Mdb::table("sys_organ").where_add_value(&val3).all::<SysOrgan>().await?;
    ///```
    ///
    pub fn where_add_value(self, value: &Value) -> Self {
        if let Some(m) = value.as_object() {
            self.where_add_map(m)
        } else {
            self
        }
    }

    ///
    /// 通过 map （Map<String, serde_json::Value>） 添加查询条件
    /// ```
    /// let mut val4 = serde_json::Map::new();
    /// val4.insert("name = ?".to_string() , json!("王大雷"));
    /// val4.insert("name_py = ?".to_string() , json!("wdl"));
    /// val4.insert("password like ?".to_string() , json!("%9845%"));
    /// val4.insert("status = ?".to_string() , json!(1));
    /// val4.insert("id in (?)".to_string() , json!([3,2,1,4]));
    /// val4.insert("pid between ? and ?".to_string() , json!([1,4]));
    /// let rows3 = Mdb::table("sys_organ")
    ///     .where_add_map(&val4)
    ///     .all::<SysOrgan>()
    ///     .await?;
    /// ```
    pub fn where_add_map(mut self, map: &Map<String, Value>) -> Self {
        for (s, k) in map {
            if k.is_array() {
                // 如果某项查询条件中参数是数组则表示查询条件为 "xxx in (?)" 或 "xxx between ? and ?"
                if let Some(kv) = k.as_array() {
                    if !util_string::is_empty(&self.where_str) {
                        self.where_str.push_str(" and ");
                    }
                    if util_string::find_char_count(s, '?') == 2 {
                        // 如果有两个 ？ ，则表示查询条件为 "xxx between ? and ?"
                        self.where_str.push_str(s);
                        match &kv[0] {
                            Value::String(v) => {
                                self.where_var.push(MyValue::String(v.to_string()));
                            }
                            Value::Number(v) => {
                                if v.is_f64() {
                                    if let Some(f) = v.as_f64() {
                                        self.where_var.push(MyValue::F64(f));
                                    }
                                } else {
                                    if let Some(i) = v.as_i64() {
                                        self.where_var.push(MyValue::I64(i));
                                    }
                                }
                            }
                            _ => {
                                self.where_var.push(MyValue::I64(0));
                            }
                        }

                        match &kv[1] {
                            Value::String(v) => {
                                self.where_var.push(MyValue::String(v.to_string()));
                            }
                            Value::Number(v) => {
                                if v.is_f64() {
                                    if let Some(f) = v.as_f64() {
                                        self.where_var.push(MyValue::F64(f));
                                    }
                                } else {
                                    if let Some(i) = v.as_i64() {
                                        self.where_var.push(MyValue::I64(i));
                                    }
                                }
                            }
                            _ => {
                                self.where_var.push(MyValue::I64(0));
                            }
                        }
                    } else {
                        // 否则认为是 "xxx in (?)" 。如果是 in 查询条件则强制转换为字符串格式如 "xxx in (1,2,3,4)"
                        let str = s.replace("?", util_string::jvec_to_string(kv, ",").as_str());
                        self.where_str.push_str(str.as_str());
                    }
                }
            } else {
                // 如果某项查询条件中参数只有一个则认为是普通查询
                match &k {
                    Value::String(v) => {
                        if !util_string::is_empty(&self.where_str) {
                            self.where_str.push_str(" and ");
                        }
                        self.where_str.push_str(s);
                        self.where_var.push(MyValue::String(v.to_string()));
                    }
                    Value::Number(v) => {
                        if !util_string::is_empty(&self.where_str) {
                            self.where_str.push_str(" and ");
                        }
                        self.where_str.push_str(s);
                        if v.is_f64() {
                            if let Some(f) = v.as_f64() {
                                self.where_var.push(MyValue::F64(f));
                            }
                        } else {
                            if let Some(i) = v.as_i64() {
                                self.where_var.push(MyValue::I64(i));
                            }
                        }
                    }
                    _ => {}
                }
            }
        }
        self
    }

    /// 设置数据
    ///
    /// ```
    /// if let o = Mdb::table("test_col").data(&json!({
    ///    "col_long" : 1,
    ///    "col_str" : "w",
    ///    "col_text" : "xxxxxxxxxxxxxxxxx",
    ///    "col_double" : 3.2,
    ///    "col_price" : 23.32,
    ///    "col_ratio" : 9.2,
    /// })).insert(member_bean, false).await?;
    ///
    /// 或
    ///
    ///  let bean = &TestColOpt {
    ///     id: None,
    ///     col_str: Some("r".to_string()),
    ///     col_double: Some(dec!(3.45)),
    ///     col_price: Some(dec!(13.23)),
    ///     ext: Some("x".to_string()),
    ///  };
    ///
    /// if let Ok(o) = Mdb::table("test_col").data(bean).insert(member_bean, false).await?;
    ///
    /// ```
    pub fn data<T: DeserializeOwned + Serialize + Debug>(mut self, bean: &T) -> Self {
        if let Ok((name, keys, values)) = util_struct::get_struct_info_by_json(bean) {
            let _table_name = util_string::camel_to_snake(name.as_str()); // 驼峰命名转蛇形命名

            //log::debug!("vec:{:?}", values);

            for (i, k) in keys.iter().enumerate() {
                //log::debug!("下标: {}, 字段名: {}", i, k);
                match &values[i] {
                    Value::Number(v) => {
                        if v.is_f64() {
                            if let Some(f) = v.as_f64() {
                                self.save_str.push_str(format!(", {} = ?", k).as_str());
                                self.save_var.push(MyValue::F64(f));
                                self.save_log_str.push_str(format!("{}={}, ", k, v).as_str())
                            }
                        } else {
                            if let Some(i) = v.as_i64() {
                                self.save_str.push_str(format!(", {} = ?", k).as_str());
                                self.save_var.push(MyValue::I64(i));
                                self.save_log_str.push_str(format!("{}={}, ", k, v).as_str())
                            }
                        }
                    }
                    Value::String(v) => {
                        self.save_str.push_str(format!(", {} = ?", k).as_str());
                        self.save_var.push(MyValue::String(v.to_string()));
                        self.save_log_str.push_str(format!("{}={}, ", k, v).as_str())
                    }
                    _ => (),
                }
            }

            self.save_str = self.save_str.trim_matches(',').to_string();

            //log::debug!("得到更新字段字符串: {}", self.save_str);
        }

        self
    }

    /// 获取一条数据
    pub async fn one<T: DeserializeOwned + Debug>(self) -> Result<Option<T>, MyErr> {
        let sql = create_one_sql_by_mdb(&self);

        //let pool = MySqlPoolOptions::new().max_connections(5).connect("mysql://laobudb:Laobu%23851124@rm-cn-4xl3k22il000h4xo.rwlb.rds.aliyuncs.com:3306/car_record").await.unwrap();
        let pool = get_pool()?;

        let mut query = sqlx::query(&sql);
        for value in &self.where_var {
            if value.is_integer() {
                if let Some(v) = value.as_i64() {
                    query = query.bind(v);
                }
            } else if value.is_float() {
                if let Some(v) = value.as_float() {
                    query = query.bind(v);
                }
            } else if value.is_str() {
                if let Some(v) = value.as_str() {
                    query = query.bind(v);
                }
            }
        }

        let rs = query.fetch_one(pool).await;
        match rs {
            Ok(v) => {
                if self.print_log {
                    log::info!(
                        "\n\n ####DB##### : [[[ sql:{}, values:{:?}, 查询结果：{}条 ]]] ### \n\n",
                        &sql,
                        &self.where_var,
                        1
                    );
                }

                let json_data = read_row_data(&v);
                let my_struct: T = serde_json::from_value(json_data).unwrap();

                Ok(Some(my_struct))
            }
            Err(e) => {
                if self.print_log {
                    log::info!(
                        "\n\n ####DB##### : [[[ sql:{}, values:{:?}, 查询结果：0条 错误:{:?} ]]] ### \n\n",
                        &sql,
                        &self.where_var,
                        e
                    );
                }
                match e {
                    sqlx::Error::RowNotFound => Ok(None),
                    _ => Err(MyErr::Db(e)),
                }
            }
        }
    }

    /// 强制获取一条数据。没有数据则会报错
    /// 如果没有数据则会报错。必须有数据：如ID查询等必须有数据的情况
    pub async fn one_force<T: DeserializeOwned + Debug>(self) -> Result<T, MyErr> {
        let sql = create_one_sql_by_mdb(&self);

        //let pool = MySqlPoolOptions::new().max_connections(5).connect("mysql://laobudb:Laobu%23851124@rm-cn-4xl3k22il000h4xo.rwlb.rds.aliyuncs.com:3306/car_record").await.unwrap();
        let pool = get_pool()?;

        let mut query = sqlx::query(&sql);
        for value in &self.where_var {
            if value.is_integer() {
                if let Some(v) = value.as_i64() {
                    query = query.bind(v);
                }
            } else if value.is_float() {
                if let Some(v) = value.as_float() {
                    query = query.bind(v);
                }
            } else if value.is_str() {
                if let Some(v) = value.as_str() {
                    query = query.bind(v);
                }
            }
        }

        let rs = query.fetch_one(pool).await;
        match rs {
            Ok(row) => {
                if self.print_log {
                    log::info!(
                        "\n\n ####DB##### : [[[ sql:{}, values:{:?}, 查询结果：{}条 ]]] ### \n\n",
                        &sql,
                        &self.where_var,
                        1
                    );
                }

                let json_data = read_row_data(&row);
                let my_struct: T = serde_json::from_value(json_data).unwrap();

                Ok(my_struct)
            }
            Err(e) => match e {
                sqlx::Error::RowNotFound => {
                    log::error!("读取不到指定的数据:{}", sql);
                    Err(MyErr::Msg("数据库中读取不到指定的数据！".to_string()))
                }
                _ => Err(MyErr::Msg(format!("错误:{:?}", e))),
            },
        }
    }

    /// 强制获取一条数据并锁定修改。没有数据则会报错
    /// 如果没有数据则会报错。必须有数据：如ID查询等必须有数据的情况
    ///
    /// ```
    /// let mut tx = my_dbs::get_transaction().await?;
    /// let verify_opt =
    /// Mdb::table("verify_process").tx_lock_one_by_id::<VerifyProcessOpt>(&mut tx, process_id).await?;
    /// let mut new_process = VerifyProcessOpt::default();
    /// new_process.disagree_num = Some(verify_opt.disagree_num.unwrap_or_default() + 1);
    /// new_process.verify_status = Some(-1);
    /// Mdb::table("verify_process")
    ///     .data(&new_process)
    ///     .tx_updae_by_id(&mut tx, &member_bean, process_id, false)
    ///     .await?;
    /// ```
    pub async fn tx_lock_one_by_id<T: DeserializeOwned + Debug>(self, tx: &mut Transaction<'static, MySql>, id: i64) -> Result<T, MyErr> {
        let sql = create_one_sql_by_id_and_for_update(&self);

        //let pool = MySqlPoolOptions::new().max_connections(5).connect("mysql://laobudb:Laobu%23851124@rm-cn-4xl3k22il000h4xo.rwlb.rds.aliyuncs.com:3306/car_record").await.unwrap();

        let mut query = sqlx::query(&sql);
        query = query.bind(id);

        let row = query.fetch_one(tx.as_mut()).await?;

        if self.print_log {
            log::info!(
                "\n\n ####DB##### : [[[ sql:{}, values:{:?}, 查询结果：{}条 ]]] ### \n\n",
                &sql,
                &self.where_var,
                1
            );
        }

        let json_data = read_row_data(&row);
        let my_struct: T = serde_json::from_value(json_data).unwrap();

        Ok(my_struct)
    }

    /// 获取所有数据
    pub async fn all<T: DeserializeOwned + Debug>(self) -> Result<Vec<T>, MyErr> {
        let sql = create_sql_by_mdb(&self);

        //let pool = MySqlPoolOptions::new().max_connections(5).connect("mysql://laobudb:Laobu%23851124@rm-cn-4xl3k22il000h4xo.rwlb.rds.aliyuncs.com:3306/car_record").await.unwrap();
        let pool = get_pool()?;

        let mut query = sqlx::query(&sql);
        for value in &self.where_var {
            if value.is_integer() {
                if let Some(v) = value.as_i64() {
                    query = query.bind(v);
                }
            } else if value.is_float() {
                if let Some(v) = value.as_float() {
                    query = query.bind(v);
                }
            } else if value.is_str() {
                if let Some(v) = value.as_str() {
                    query = query.bind(v);
                }
            }
        }

        let rows = query.fetch_all(pool).await?;

        if self.print_log {
            log::info!(
                "\n\n ####DB##### sql:{}, values:{:?}, 查询结果：{}条 ### \n\n",
                &sql,
                &self.where_var,
                rows.len()
            );
        }

        let arry: Vec<_> = rows
            .into_iter()
            .map(|row| {
                let json_data = read_row_data(&row);
                let my_struct: T = serde_json::from_value(json_data).unwrap();
                my_struct
            })
            .collect();

        //println!("");

        //println!("结果:{:?}", arry);

        Ok(arry)
    }

    /// 通过条件语句及参数添加查询条件
    /// ```
    ///
    /// let o = Mdb::query::<Value>("select * from test_col where id = ? and status > ?", json!([4, 0])).await?;
    ///
    /// // -------
    ///
    /// let mut args: Vec<serde_json::Value> = Vec::new(); // sql语句中的值参数
    /// args.push(json!(1));
    /// args.push(json!("xxx"));
    /// let mut list = Mdb::query::<Value>("select * from test_col where id = ? and name = ?", Value::Array(args)).await?;
    ///
    /// ```
    ///
    #[allow(dead_code)]
    pub async fn query<T: DeserializeOwned + Debug>(where_str: &str, arry: Value) -> Result<Vec<T>, MyErr> {
        let pool = get_pool()?;

        let mut query = sqlx::query(where_str);
        if let Some(items) = arry.as_array() {
            for item in items {
                if item.is_i64() {
                    query = query.bind(item.as_i64().unwrap_or_default());
                } else if item.is_f64() {
                    query = query.bind(item.as_f64().unwrap_or(0.0));
                } else if item.is_string() {
                    query = query.bind(item.as_str().unwrap_or_default());
                }
            }
        }

        let rows = query.fetch_all(pool).await?;

        log::info!(
            "\n\n ####DB##### sql:{}, values:{:?}, 查询结果：{}条 ### \n\n",
            where_str,
            arry,
            rows.len()
        );

        let arry: Vec<_> = rows
            .into_iter()
            .map(|row| {
                let json_data = read_row_data(&row);
                let my_struct: T = serde_json::from_value(json_data).unwrap();
                my_struct
            })
            .collect();

        Ok(arry)
    }

    /// 获取统计条数
    pub async fn count(self) -> Result<i64, MyErr> {
        let sql = create_count_sql_by_mdb(&self);

        //let pool = MySqlPoolOptions::new().max_connections(5).connect("mysql://laobudb:Laobu%23851124@rm-cn-4xl3k22il000h4xo.rwlb.rds.aliyuncs.com:3306/car_record").await.unwrap();
        let pool = get_pool()?;

        let mut query = sqlx::query(&sql);
        for value in &self.where_var {
            if value.is_integer() {
                if let Some(v) = value.as_i64() {
                    query = query.bind(v);
                }
            } else if value.is_float() {
                if let Some(v) = value.as_float() {
                    query = query.bind(v);
                }
            } else if value.is_str() {
                if let Some(v) = value.as_str() {
                    query = query.bind(v);
                }
            }
        }

        let row = query.fetch_one(pool).await?;

        if self.print_log {
            log::info!(
                "\n\n ####DB##### sql:{}, values:{:?}, 查询结果：{:?}  ### \n\n",
                &sql,
                &self.where_var,
                row
            );
        }

        let json_data = read_row_data(&row);
        if let Some(cnt) = json_data.get("cnt") {
            if cnt.is_number() {
                if let Some(cnt_val) = cnt.as_i64() {
                    Ok(cnt_val)
                } else {
                    Err(MyErr::Msg("错误的统计结果".to_string()))
                }
            } else {
                let cnt_text = cnt.as_str().unwrap_or_default();
                if let Ok(cnt_val) = cnt_text.parse::<i64>() {
                    Ok(cnt_val)
                } else {
                    Err(MyErr::Msg("错误的统计结果".to_string()))
                }
            }
        } else {
            Err(MyErr::Msg("统计语句有误".to_string()))
        }
    }

    /// 插入数据
    /// 参数：
    /// member_bean：操作修改的管理员
    /// log_flag：是否对本次操作进行记录
    ///
    /// 返回结果，Result<(更新数据条数, 最后ID), error>
    ///
    /// ```
    /// if let o = Mdb::table("test_col").data(&json!({
    ///    "col_long" : 1,
    ///    "col_str" : "w",
    ///    "col_text" : "xxxxxxxxxxxxxxxxx",
    ///    "col_double" : 3.2,
    ///    "col_price" : 23.32,
    ///    "col_ratio" : 9.2,
    /// })).insert(member_bean, false).await?;
    ///
    /// 或
    ///
    ///  let bean = &TestColOpt {
    ///     id: None,
    ///     col_str: Some("r".to_string()),
    ///     col_double: Some(dec!(3.45)),
    ///     col_price: Some(dec!(13.23)),
    ///     ext: Some("x".to_string()),
    ///  };
    ///
    /// if let Ok(o) = Mdb::table("test_col").data(bean).insert(member_bean, false).await?;
    ///
    /// ```
    pub async fn insert<T: UserTrait>(self, user: &T, log_flag: bool) -> Result<(i32, i64), MyErr> {
        let pool = get_pool()?;

        let sql = create_insert_sql_by_mdb(&self);

        if self.print_log {
            log::info!("\n\n ####DB##### sql:{}, values:{:?} ### \n\n", &sql, &self.save_var);
        }

        let mut query = sqlx::query(&sql);

        for value in self.save_var {
            if value.is_integer() {
                if let Some(v) = value.as_i64() {
                    query = query.bind(v);
                }
            } else if value.is_float() {
                if let Some(v) = value.as_float() {
                    query = query.bind(v);
                }
            } else if value.is_str() {
                if let Some(v) = value.as_str() {
                    query = query.bind(v);
                }
            }
        }

        let rs = query.execute(pool).await;
        match rs {
            Ok(rows) => {
                if log_flag && rows.rows_affected() > 0 {
                    // 如果需要记录
                    // 如果需要记录
                    let _ = sqlx::query("insert into sys_modify_record set member_id = ?, member_name = ?, modify_event = ?, modify_table = ?, modify_id = ?, modify_info = ?, create_time = ?, status = 1 ")
                        .bind(user.get_id())
                        .bind(user.get_name())
                        .bind("insert")
                        .bind(self.table.clone())
                        .bind(rows.last_insert_id())
                        .bind(&self.save_log_str)
                        .bind(util_date::now_milliseconds())
                        .execute(pool)
                        .await;
                }

                Ok((rows.rows_affected() as i32, rows.last_insert_id() as i64))
            }
            Err(e) => {
                log::debug!("插入数据错误：{:?}", e);
                Err(MyErr::Db(e))
            }
        }
    }

    /// 使用事务处理-插入数据
    /// 参数：
    /// member_bean：操作修改的管理员
    /// log_flag：是否对本次操作进行记录
    ///
    /// 返回结果，Result<(更新数据条数, 最后ID), error>
    ///
    /// ```
    /// let mut tx = databases::get_transaction().await?;
    ///
    /// if let o = Mdb::table("test_col").data(&json!({
    ///    "col_long" : 1,
    ///    "col_str" : "w",
    ///    "col_text" : "xxxxxxxxxxxxxxxxx",
    ///    "col_double" : 3.2,
    ///    "col_price" : 23.32,
    ///    "col_ratio" : 9.2,
    /// })).tx_insert(&mut tx, member_bean, false).await?;
    ///
    /// let _ = tx.commit().await;  // 只有执行了该语句才会提交改变，否则自动回滚，如果此之前代码报错退出也会自动回滚
    ///
    /// // tx.rollback().await; //手动回滚
    ///
    /// 或
    ///
    /// let mut tx = databases::get_transaction().await?;
    ///
    /// let bean = &TestColOpt {
    ///    id: None,
    ///    col_str: Some("r".to_string()),
    ///    col_double: Some(dec!(3.45)),
    ///    col_price: Some(dec!(13.23)),
    ///    ext: Some("x".to_string()),
    /// };
    ///
    /// if let Ok(o) = Mdb::table("test_col").data(bean).tx_insert(&mut tx, member_bean, false).await?;
    ///
    /// let _ = tx.commit().await;  // 只有执行了该语句才会提交改变，否则自动回滚，如果此之前代码报错退出也会自动回滚
    ///
    /// // tx.rollback().await; //手动回滚
    ///
    /// ```
    #[allow(dead_code)]
    pub async fn tx_insert<T: UserTrait>(
        self,
        tx: &mut Transaction<'static, MySql>,
        user: &T,
        log_flag: bool,
    ) -> Result<(i32, i64), MyErr> {
        let sql = create_insert_sql_by_mdb(&self);

        if self.print_log {
            log::info!("\n\n ####DB##### sql:{}, values:{:?} ### \n\n", &sql, &self.save_var);
        }

        let mut query = sqlx::query(&sql);

        for value in self.save_var {
            if value.is_integer() {
                if let Some(v) = value.as_i64() {
                    query = query.bind(v);
                }
            } else if value.is_float() {
                if let Some(v) = value.as_float() {
                    query = query.bind(v);
                }
            } else if value.is_str() {
                if let Some(v) = value.as_str() {
                    query = query.bind(v);
                }
            }
        }

        let rs = query.execute(tx.as_mut()).await;
        match rs {
            Ok(rows) => {
                if log_flag && rows.rows_affected() > 0 {
                    // 如果需要记录
                    if let Ok(pool) = get_pool() {
                        let _ = sqlx::query("insert into sys_modify_record set member_id = ?, member_name = ?, modify_event = ?, modify_table = ?, modify_id = ?, modify_info = ?, create_time = ?, status = 1 ")
                        .bind(user.get_id())
                        .bind(user.get_name())
                        .bind("insert")
                        .bind(self.table.clone())
                        .bind(rows.last_insert_id())
                        .bind(&self.save_log_str)
                        .bind(util_date::now_milliseconds())
                        .execute(pool)
                        .await;
                    }
                }

                Ok((rows.rows_affected() as i32, rows.last_insert_id() as i64))
            }
            Err(e) => {
                log::debug!("插入数据错误：{:?}", e);
                Err(MyErr::Db(e))
            }
        }
    }

    /// 使用事务处理-批量插入数据
    /// 注意：如果使用了 数据源struct，里面的某些项目使用了 Option() 的时候会导致空值，以至于数据项不匹配而导入失败。
    /// 批量插入的时候应尽量避免 Option() 的 None 值。
    /// 参数：
    /// member_bean：操作修改的管理员
    /// log_flag：是否对本次操作进行记录
    ///
    /// 返回结果，Result<(更新数据条数, 最后ID), error>
    ///
    /// ```
    /// let mut tx = databases::get_transaction().await?;
    ///
    /// if let o = Mdb::table("test_col").data(&json!({
    ///    "col_long" : 1,
    ///    "col_str" : "w",
    ///    "col_text" : "xxxxxxxxxxxxxxxxx",
    ///    "col_double" : 3.2,
    ///    "col_price" : 23.32,
    ///    "col_ratio" : 9.2,
    /// })).tx_insert(&mut tx, member_bean, false).await?;
    ///
    /// let _ = tx.commit().await;  // 只有执行了该语句才会提交改变，否则自动回滚，如果此之前代码报错退出也会自动回滚
    ///
    /// // tx.rollback().await; //手动回滚
    ///
    /// 或
    ///
    /// let mut tx = databases::get_transaction().await?;
    ///
    /// let bean = &TestColOpt {
    ///    id: None,
    ///    col_str: Some("r".to_string()),
    ///    col_double: Some(dec!(3.45)),
    ///    col_price: Some(dec!(13.23)),
    ///    ext: Some("x".to_string()),
    /// };
    ///
    /// if let Ok(o) = Mdb::table("test_col").data(bean).tx_insert(&mut tx, member_bean, false).await?;
    ///
    /// let _ = tx.commit().await;  // 只有执行了该语句才会提交改变，否则自动回滚，如果此之前代码报错退出也会自动回滚
    ///
    /// // tx.rollback().await; //手动回滚
    ///
    /// ```
    #[allow(dead_code)]
    pub async fn tx_batch_insert<T: DeserializeOwned + Serialize + Debug>(
        self,
        tx: &mut Transaction<'static, MySql>,
        datas: &Vec<T>,
        len: usize,
    ) -> Result<i32, MyErr> {
        let data_len = datas.len();
        let sqls = create_batch_insert_sql_by_mdb(&self, datas, len);

        for sql in sqls {
            if self.print_log {
                log::info!("\n\n ####DB##### sql:{} ### \n\n", sql);
            }

            let query = sqlx::query(&sql);
            let _ = query.execute(tx.as_mut()).await?;
        }

        Ok(data_len as i32)
    }

    /// 更新数据，根据ID修改数据
    /// 参数：
    /// member_bean：操作修改的管理员
    /// id：需要修改的数据ID
    /// log_flag：是否对本次操作进行记录
    /// 返回结果，Result<(更新数据条数, 最后ID), error>
    ///
    /// ```
    /// if let o = Mdb::table("test_col").data(&json!({
    ///    "col_text" : "xxxxxxxxxxxxxxxxx",
    ///    "col_double" : 3.2,
    /// })).updae_by_id(member_bean, 5, false).await?;
    ///
    /// 或
    ///
    ///   
    ///  let bean = TestColOpt::TestColOpt::default()；
    ///  bean.col_str = Some("r".to_string());
    ///
    ///  if let Ok(o) = Mdb::table("test_col").data(&bean).updae_by_id(member_bean, 6, false).await?;
    ///
    /// ```
    ///
    pub async fn updae_by_id<T: UserTrait>(&mut self, user: &T, id: i64, log_flag: bool) -> Result<(i32, i64), MyErr> {
        if id > 0 {
            let pool = get_pool()?;

            let sql = create_update_id_sql_by_mdb(&self);

            if self.print_log {
                log::info!("\n\n ####DB##### sql:{}, values:{:?} id:{} ### \n\n", &sql, &self.save_var, id);
            }

            let mut query = sqlx::query(&sql);

            for value in &self.save_var {
                if value.is_integer() {
                    if let Some(v) = value.as_i64() {
                        query = query.bind(v);
                    }
                } else if value.is_float() {
                    if let Some(v) = value.as_float() {
                        query = query.bind(v);
                    }
                } else if value.is_str() {
                    if let Some(v) = value.as_str() {
                        query = query.bind(v);
                    }
                }
            }

            // 添加最后的参数 id = ？，ID
            query = query.bind(id);

            let rs = query.execute(pool).await;
            match rs {
                Ok(rows) => {
                    if log_flag && rows.rows_affected() > 0 {
                        // 如果需要记录
                        let _ =
                            sqlx::query("insert into sys_modify_record set member_id = ?, member_name = ?, modify_event = ?, modify_table = ?, modify_id = ?, modify_info = ?, create_time = ?, status = 1 ")
                                .bind(user.get_id())
                                .bind(user.get_name())
                                .bind("update")
                                .bind(self.table.clone())
                                .bind(id)
                                .bind(&self.save_log_str)
                                .bind(util_date::now_milliseconds())
                                .execute(pool)
                                .await;
                    }

                    Ok((rows.rows_affected() as i32, id))
                }
                Err(e) => {
                    log::debug!("更新数据错误：{:?}", e);
                    Err(MyErr::Db(e))
                }
            }
        } else {
            Err(MyErr::Msg("错误的ID参数".to_string()))
        }
    }

    /// 使用事务处理 - 更新数据，根据ID修改数据
    /// 参数：
    /// member_bean：操作修改的管理员
    /// id：需要修改的数据ID
    /// log_flag：是否对本次操作进行记录
    /// 返回结果，Result<(更新数据条数, 最后ID), error>
    ///
    /// ```
    /// let mut tx = databases::get_transaction().await?;
    ///
    /// if let o = Mdb::table("test_col").data(&json!({
    ///    "col_text" : "xxxxxxxxxxxxxxxxx",
    ///    "col_double" : 3.2,
    /// })).tx_updae_by_id(&mut tx, member_bean, 5, false).await?;
    ///
    /// let _ = tx.commit().await;  // 只有执行了该语句才会提交改变，否则自动回滚，如果此之前代码报错退出也会自动回滚
    ///
    /// // tx.rollback().await; //手动回滚
    ///
    /// 或
    ///
    ///   
    ///  let bean = TestColOpt::TestColOpt::default()；
    ///  bean.col_str = Some("r".to_string());
    ///
    ///  let mut tx = databases::get_transaction().await?;
    ///
    ///  if let Ok(o) = Mdb::table("test_col").data(&bean).tx_updae_by_id(&mut tx, member_bean, 5, false).await?;
    ///
    ///  let _ = tx.commit().await;  // 只有执行了该语句才会提交改变，否则自动回滚，如果此之前代码报错退出也会自动回滚
    ///
    /// // tx.rollback().await; //手动回滚
    /// ```
    ///
    #[allow(dead_code)]
    pub async fn tx_updae_by_id<T: UserTrait>(
        &mut self,
        tx: &mut Transaction<'static, MySql>,
        user: &T,
        id: i64,
        log_flag: bool,
    ) -> Result<(i32, i64), MyErr> {
        if id > 0 {
            let sql = create_update_id_sql_by_mdb(&self);

            if self.print_log {
                log::info!("\n\n ####DB##### sql:{}, values:{:?} id:{} ### \n\n", &sql, &self.save_var, id);
            }

            let mut query = sqlx::query(&sql);

            for value in &self.save_var {
                if value.is_integer() {
                    if let Some(v) = value.as_i64() {
                        query = query.bind(v);
                    }
                } else if value.is_float() {
                    if let Some(v) = value.as_float() {
                        query = query.bind(v);
                    }
                } else if value.is_str() {
                    if let Some(v) = value.as_str() {
                        query = query.bind(v);
                    }
                }
            }

            // 添加最后的参数 id = ？，ID
            query = query.bind(id);

            let rs = query.execute(tx.as_mut()).await;
            match rs {
                Ok(rows) => {
                    if log_flag && rows.rows_affected() > 0 {
                        // 如果需要记录
                        if let Ok(pool) = get_pool() {
                            let _ =
                            sqlx::query("insert into sys_modify_record set member_id = ?, member_name = ?, modify_event = ?, modify_table = ?, modify_id = ?, modify_info = ?, create_time = ?, status = 1 ")
                                .bind(user.get_id())
                                .bind(user.get_name())
                                .bind("update")
                                .bind(self.table.clone())
                                .bind(id)
                                .bind(&self.save_log_str)
                                .bind(util_date::now_milliseconds())
                                .execute(pool)
                                .await;
                        }
                    }

                    Ok((rows.rows_affected() as i32, id))
                }
                Err(e) => {
                    log::debug!("更新数据错误：{:?}", e);
                    Err(MyErr::Db(e))
                }
            }
        } else {
            Err(MyErr::Msg("错误的ID参数".to_string()))
        }
    }

    /// 更新数据，根据ID修改数据
    /// 参数：
    /// member_bean：操作修改的管理员
    /// id：需要修改的数据ID
    /// log_flag：是否对本次操作进行记录
    /// 返回结果，Result<(更新数据条数, 最后ID), error>
    ///
    /// ```
    /// if let o = Mdb::table("test_col").data(&json!({
    ///    "col_text" : "xxxxxxxxxxxxxxxxx",
    ///    "col_double" : 3.2,
    /// })).where_add_str("status = ? and sort = ?", json!([1, 0]))
    ///     .updae_by_where(member_bean, 5, false).await?;
    ///
    /// 或
    ///
    ///   
    ///  let bean = TestColOpt::TestColOpt::default()；
    ///  bean.col_str = Some("r".to_string());
    ///
    ///  if let Ok(o) = Mdb::table("test_col").data(&bean)
    ///     .where_add_str("status = ? and sort = ?", json!([1, 0]))
    ///     .updae_by_where(member_bean, false).await?;
    ///
    /// ```
    ///
    #[allow(dead_code)]
    pub async fn updae_by_where<T: UserTrait>(&mut self, user: &T, log_flag: bool) -> Result<(i32, i32), MyErr> {
        let pool = get_pool()?;

        let sql = create_update_sql_by_mdb(&self);

        if self.print_log {
            log::info!(
                "\n\n ####DB##### sql:{}, values:{:?} where:{:?} ### \n\n",
                &sql,
                &self.save_var,
                &self.where_var
            );
        }

        let mut query = sqlx::query(&sql);

        for value in &self.save_var {
            if value.is_integer() {
                if let Some(v) = value.as_i64() {
                    query = query.bind(v);
                }
            } else if value.is_float() {
                if let Some(v) = value.as_float() {
                    query = query.bind(v);
                }
            } else if value.is_str() {
                if let Some(v) = value.as_str() {
                    query = query.bind(v);
                }
            }
        }

        // 添加 where 参数
        for value in &self.where_var {
            if value.is_integer() {
                if let Some(v) = value.as_i64() {
                    query = query.bind(v);
                }
            } else if value.is_float() {
                if let Some(v) = value.as_float() {
                    query = query.bind(v);
                }
            } else if value.is_str() {
                if let Some(v) = value.as_str() {
                    query = query.bind(v);
                }
            }
        }

        let rs = query.execute(pool).await;
        match rs {
            Ok(rows) => {
                if log_flag && rows.rows_affected() > 0 {
                    // 如果需要记录
                    let _ =
                            sqlx::query("insert into sys_modify_record set member_id = ?, member_name = ?, modify_event = ?, modify_table = ?, modify_id = ?, modify_info = ?, create_time = ?, status = 1 ")
                                .bind(user.get_id())
                                .bind(user.get_name())
                                .bind("update")
                                .bind(self.table.clone())
                                .bind(0)
                                .bind(&self.save_log_str)
                                .bind(util_date::now_milliseconds())
                                .execute(pool)
                                .await;
                }

                Ok((rows.rows_affected() as i32, 0))
            }
            Err(e) => {
                log::debug!("更新数据错误：{:?}", e);
                Err(MyErr::Db(e))
            }
        }
    }

    /// 使用事务处理 - 更新数据，根据ID修改数据
    /// 参数：
    /// member_bean：操作修改的管理员
    /// id：需要修改的数据ID
    /// log_flag：是否对本次操作进行记录
    /// 返回结果，Result<(更新数据条数, 最后ID), error>
    ///
    /// ```
    /// let mut tx = databases::get_transaction().await?;
    ///
    /// if let o = Mdb::table("test_col").data(&json!({
    ///    "col_text" : "xxxxxxxxxxxxxxxxx",
    ///    "col_double" : 3.2,
    /// })).where_add_str("status = ? and sort = ?", json!([1, 0])).tx_updae_by_where(&mut tx, member_bean, false).await?;
    ///
    /// let _ = tx.commit().await;  // 只有执行了该语句才会提交改变，否则自动回滚，如果此之前代码报错退出也会自动回滚
    ///
    /// // tx.rollback().await; //手动回滚
    ///
    /// 或
    ///
    ///   
    ///  let bean = TestColOpt::TestColOpt::default()；
    ///  bean.col_str = Some("r".to_string());
    ///
    ///  let mut tx = databases::get_transaction().await?;
    ///
    ///  if let Ok(o) = Mdb::table("test_col").data(&bean).where_add_str("status = ? and sort = ?", json!([1, 0])).tx_updae_by_where(&mut tx, member_bean, false).await?;
    ///
    ///  let _ = tx.commit().await;  // 只有执行了该语句才会提交改变，否则自动回滚，如果此之前代码报错退出也会自动回滚
    ///
    /// // tx.rollback().await; //手动回滚
    /// ```
    ///
    #[allow(dead_code)]
    pub async fn tx_updae_by_where<T: UserTrait>(
        &mut self,
        tx: &mut Transaction<'static, MySql>,
        user: &T,
        log_flag: bool,
    ) -> Result<(i32, i32), MyErr> {
        let sql = create_update_sql_by_mdb(&self);

        if self.print_log {
            log::info!(
                "\n\n ####DB##### sql:{}, values:{:?} where:{:?} ### \n\n",
                &sql,
                &self.save_var,
                &self.where_var
            );
        }

        let mut query = sqlx::query(&sql);

        for value in &self.save_var {
            if value.is_integer() {
                if let Some(v) = value.as_i64() {
                    query = query.bind(v);
                }
            } else if value.is_float() {
                if let Some(v) = value.as_float() {
                    query = query.bind(v);
                }
            } else if value.is_str() {
                if let Some(v) = value.as_str() {
                    query = query.bind(v);
                }
            }
        }

        // 添加 where 参数
        for value in &self.where_var {
            if value.is_integer() {
                if let Some(v) = value.as_i64() {
                    query = query.bind(v);
                }
            } else if value.is_float() {
                if let Some(v) = value.as_float() {
                    query = query.bind(v);
                }
            } else if value.is_str() {
                if let Some(v) = value.as_str() {
                    query = query.bind(v);
                }
            }
        }

        let rs = query.execute(tx.as_mut()).await;
        match rs {
            Ok(rows) => {
                if log_flag && rows.rows_affected() > 0 {
                    // 如果需要记录
                    if let Ok(pool) = get_pool() {
                        let _ =
                            sqlx::query("insert into sys_modify_record set member_id = ?, member_name = ?, modify_event = ?, modify_table = ?, modify_id = ?, modify_info = ?, create_time = ?, status = 1 ")
                                .bind(user.get_id())
                                .bind(user.get_name())
                                .bind("update")
                                .bind(self.table.clone())
                                .bind(0)
                                .bind(&self.save_log_str)
                                .bind(util_date::now_milliseconds())
                                .execute(pool)
                                .await;
                    }
                }

                Ok((rows.rows_affected() as i32, 0))
            }
            Err(e) => {
                log::debug!("更新数据错误：{:?}", e);
                Err(MyErr::Db(e))
            }
        }
    }

    /// 删除数据，根据ID删除数据
    /// 参数：
    /// member_bean：操作修改的管理员
    /// id：需要修改的数据ID
    /// log_flag：是否对本次操作进行记录
    /// 返回结果，Result<(更新数据条数, 最后ID), error>
    ///
    /// ```
    /// if let o = Mdb::table("test_col").data(&json!({
    ///    "col_text" : "xxxxxxxxxxxxxxxxx",
    ///    "col_double" : 3.2,
    /// })).delete_by_id(member_bean, 5, false).await?;
    ///
    /// 或
    ///
    ///   
    ///  let bean = TestColOpt::TestColOpt::default()；
    ///  bean.col_str = Some("r".to_string());
    ///
    ///  if let Ok(o) = Mdb::table("test_col").data(&bean).delete_by_id(member_bean, 6, false).await?;
    ///
    /// ```
    ///
    pub async fn delete_by_id<T: UserTrait>(&mut self, user: &T, id: i64, log_flag: bool) -> Result<(i32, i64), MyErr> {
        if id > 0 {
            let pool = get_pool()?;

            let sql = create_delete_id_sql_by_mdb(&self);

            if self.print_log {
                log::info!("\n\n ####DB##### sql:{}, id:{} ### \n\n", &sql, id);
            }

            let mut query = sqlx::query(&sql);

            // 添加最后的参数 id = ？，ID
            query = query.bind(id);

            let rs = query.execute(pool).await;
            match rs {
                Ok(rows) => {
                    if log_flag && rows.rows_affected() > 0 {
                        // 如果需要记录
                        let _ =
                            sqlx::query("insert into sys_modify_record set member_id = ?, member_name = ?, modify_event = ?, modify_table = ?, modify_id = ?, modify_info = ?, create_time = ?, status = 1 ")
                                .bind(user.get_id())
                                .bind(user.get_name())
                                .bind("delete")
                                .bind(self.table.clone())
                                .bind(id)
                                .bind(&self.save_log_str)
                                .bind(util_date::now_milliseconds())
                                .execute(pool)
                                .await;
                    }

                    Ok((rows.rows_affected() as i32, id))
                }
                Err(e) => {
                    log::debug!("更新数据错误：{:?}", e);
                    Err(MyErr::Db(e))
                }
            }
        } else {
            Err(MyErr::Msg("错误的ID参数".to_string()))
        }
    }

    /// 删除数据，根据where语句删除数据
    /// 参数：
    /// member_bean：操作修改的管理员
    /// log_flag：是否对本次操作进行记录
    /// 返回结果，Result<更新数据条数, error>
    ///
    /// ```
    /// if let o = Mdb::table("test_col")
    ///     .where_add_str("status = ? and sort = ?", json!([1, 0]))
    ///     .delete_by_where(member_bean, false).await?;
    ///  
    ///
    /// ```
    ///
    #[allow(dead_code)]
    pub async fn delete_by_where<T: UserTrait>(&mut self, user: &T, log_flag: bool) -> Result<i32, MyErr> {
        let pool = get_pool()?;

        let sql = create_delete_sql_by_mdb(&self);

        if self.print_log {
            log::info!("\n\n ####DB##### sql:{}, where:{:?} ### \n\n", &sql, &self.where_var);
        }

        let mut query = sqlx::query(&sql);

        // 添加 where 参数
        for value in &self.where_var {
            if value.is_integer() {
                if let Some(v) = value.as_i64() {
                    query = query.bind(v);
                }
            } else if value.is_float() {
                if let Some(v) = value.as_float() {
                    query = query.bind(v);
                }
            } else if value.is_str() {
                if let Some(v) = value.as_str() {
                    query = query.bind(v);
                }
            }
        }

        let rs = query.execute(pool).await;
        match rs {
            Ok(rows) => {
                if log_flag && rows.rows_affected() > 0 {
                    // 如果需要记录
                    let _ =
                            sqlx::query("insert into sys_modify_record set member_id = ?, member_name = ?, modify_event = ?, modify_table = ?, modify_id = ?, modify_info = ?, create_time = ?, status = 1 ")
                                .bind(user.get_id())
                                .bind(user.get_name())
                                .bind("delete")
                                .bind(self.table.clone())
                                .bind(0)
                                .bind(&self.save_log_str)
                                .bind(util_date::now_milliseconds())
                                .execute(pool)
                                .await;
                }

                Ok(rows.rows_affected() as i32)
            }
            Err(e) => {
                log::debug!("更新数据错误：{:?}", e);
                Err(MyErr::Db(e))
            }
        }
    }

    /// 删除数据，根据ID删除数据
    /// 参数：
    /// member_bean：操作修改的管理员
    /// id：需要修改的数据ID
    /// log_flag：是否对本次操作进行记录
    /// 返回结果，Result<(更新数据条数, 最后ID), error>
    ///
    /// ```
    /// let mut tx = databases::get_transaction().await?;
    ///
    /// if let o = Mdb::table("test_col").data(&json!({
    ///    "col_text" : "xxxxxxxxxxxxxxxxx",
    ///    "col_double" : 3.2,
    /// })).tx_delete_by_id(&mut tx, member_bean, 5, false).await?;
    ///
    /// let _ = tx.commit().await; // 只有执行了该语句才会提交改变，否则自动回滚，如果此之前代码报错退出也会自动回滚
    ///
    /// // tx.rollback().await; //手动回滚
    ///
    /// 或
    ///
    /// let mut tx = databases::get_transaction().await?;
    ///   
    /// let bean = TestColOpt::TestColOpt::default()；
    /// bean.col_str = Some("r".to_string());
    ///
    /// if let Ok(o) = Mdb::table("test_col").data(&bean).tx_delete_by_id(&mut tx, member_bean, 6, false).await?;
    ///
    /// let _ = tx.commit().await; // 只有执行了该语句才会提交改变，否则自动回滚，如果此之前代码报错退出也会自动回滚
    ///
    /// // tx.rollback().await; //手动回滚
    ///
    /// ```
    ///
    #[allow(dead_code)]
    pub async fn tx_delete_by_id<T: UserTrait>(
        &mut self,
        tx: &mut Transaction<'static, MySql>,
        user: &T,
        id: i64,
        log_flag: bool,
    ) -> Result<(i32, i64), MyErr> {
        if id > 0 {
            let sql = create_delete_id_sql_by_mdb(&self);

            if self.print_log {
                log::info!("\n\n ####DB##### sql:{}, id:{} ### \n\n", &sql, id);
            }

            let mut query = sqlx::query(&sql);

            // 添加最后的参数 id = ？，ID
            query = query.bind(id);

            let rs = query.execute(tx.as_mut()).await;
            match rs {
                Ok(rows) => {
                    if log_flag && rows.rows_affected() > 0 {
                        // 如果需要记录
                        if let Ok(pool) = get_pool() {
                            let _ =
                            sqlx::query("insert into sys_modify_record set member_id = ?, member_name = ?, modify_event = ?, modify_table = ?, modify_id = ?, modify_info = ?, create_time = ?, status = 1 ")
                                .bind(user.get_id())
                                .bind(user.get_name())
                                .bind("delete")
                                .bind(self.table.clone())
                                .bind(id)
                                .bind(&self.save_log_str)
                                .bind(util_date::now_milliseconds())
                                .execute(pool)
                                .await;
                        }
                    }

                    Ok((rows.rows_affected() as i32, id))
                }
                Err(e) => {
                    log::debug!("更新数据错误：{:?}", e);
                    Err(MyErr::Db(e))
                }
            }
        } else {
            Err(MyErr::Msg("错误的ID参数".to_string()))
        }
    }

    /// 删除数据，根据ID删除数据
    /// 参数：
    /// member_bean：操作修改的管理员
    /// id：需要修改的数据ID
    /// log_flag：是否对本次操作进行记录
    /// 返回结果，Result<更新数据条数, error>
    ///
    /// ```
    /// let mut tx = databases::get_transaction().await?;
    ///
    /// if let o = Mdb::table("test_col")
    ///     .where_add_str("status = ? and sort = ?", json!([1, 0]))
    ///     .tx_delete_by_where(&mut tx, member_bean, false).await?;
    ///
    /// let _ = tx.commit().await; // 只有执行了该语句才会提交改变，否则自动回滚，如果此之前代码报错退出也会自动回滚
    ///
    /// // tx.rollback().await; //手动回滚
    ///
    /// ```
    ///
    #[allow(dead_code)]
    pub async fn tx_delete_by_where<T: UserTrait>(
        &mut self,
        tx: &mut Transaction<'static, MySql>,
        user: &T,
        log_flag: bool,
    ) -> Result<i32, MyErr> {
        let sql = create_delete_sql_by_mdb(&self);

        if self.print_log {
            log::info!("\n\n ####DB##### sql:{}, where:{:?} ### \n\n", &sql, &self.where_var);
        }

        let mut query = sqlx::query(&sql);

        // 添加 where 参数
        for value in &self.where_var {
            if value.is_integer() {
                if let Some(v) = value.as_i64() {
                    query = query.bind(v);
                }
            } else if value.is_float() {
                if let Some(v) = value.as_float() {
                    query = query.bind(v);
                }
            } else if value.is_str() {
                if let Some(v) = value.as_str() {
                    query = query.bind(v);
                }
            }
        }

        let rs = query.execute(tx.as_mut()).await;
        match rs {
            Ok(rows) => {
                if log_flag && rows.rows_affected() > 0 {
                    // 如果需要记录
                    if let Ok(pool) = get_pool() {
                        let _ =
                            sqlx::query("insert into sys_modify_record set member_id = ?, member_name = ?, modify_event = ?, modify_table = ?, modify_id = ?, modify_info = ?, create_time = ?, status = 1 ")
                                .bind(user.get_id())
                                .bind(user.get_name())
                                .bind("delete")
                                .bind(self.table.clone())
                                .bind(0)
                                .bind(&self.save_log_str)
                                .bind(util_date::now_milliseconds())
                                .execute(pool)
                                .await;
                    }
                }

                Ok(rows.rows_affected() as i32)
            }
            Err(e) => {
                log::debug!("更新数据错误：{:?}", e);
                Err(MyErr::Db(e))
            }
        }
    }
}

// 读取数据库单行数据内容，存入serde_json::Value中
fn read_row_data(row: &MySqlRow) -> Value {
    let mut json_data = Value::Object(Default::default()); // 创建空的JSON对象
    if let Some(json_obj) = json_data.as_object_mut() {
        for item in row.columns() {
            //println!("item:{:?}", item);
            let ordinal = item.ordinal();
            let col_name = item.name();
            let type_name = item.type_info().name();

            //println!("字段名称:{} 类型：{}", col_name, type_name);
            match type_name {
                "INT" => {
                    if let Ok(v) = row.try_get::<i32, usize>(ordinal) {
                        json_obj.insert(col_name.to_string(), json!(v));
                    }
                }

                "BIGINT" => {
                    if let Ok(v) = row.try_get::<i64, usize>(ordinal) {
                        json_obj.insert(col_name.to_string(), json!(v));
                    }
                }

                "VARCHAR" => {
                    if let Ok(v) = row.try_get::<String, usize>(ordinal) {
                        json_obj.insert(col_name.to_string(), json!(v));
                    }
                }

                "DECIMAL" => {
                    if let Ok(v) = row.try_get::<Decimal, usize>(ordinal) {
                        json_obj.insert(col_name.to_string(), json!(v));
                    }
                }

                "TEXT" => {
                    if let Ok(v) = row.try_get::<String, usize>(ordinal) {
                        json_obj.insert(col_name.to_string(), json!(v));
                    }
                }

                _ => {}
            }
        }
    }

    json_data
}

/// 根据mdb生成 sql 语句
fn create_sql_by_mdb(mdb: &Mdb) -> String {
    let mut sql = String::from("SELECT ");
    sql.push_str(mdb.field.as_str());
    sql.push_str(" from ");
    sql.push_str(mdb.table.as_str());
    if !util_string::is_empty(mdb.where_str.as_str()) {
        sql.push_str(" where ");
        sql.push_str(mdb.where_str.as_str());
    }
    if !util_string::is_empty(mdb.order_by.as_str()) {
        sql.push_str(" order by ");
        sql.push_str(mdb.order_by.as_str());
    }
    if !util_string::is_empty(mdb.limit.as_str()) {
        sql.push_str(" limit ");
        sql.push_str(mdb.limit.as_str());
    }
    sql
}

/// 根据mdb生成 sql 语句(只取一条数据)
fn create_one_sql_by_mdb(mdb: &Mdb) -> String {
    let mut sql = String::from("SELECT ");
    sql.push_str(mdb.field.as_str());
    sql.push_str(" from ");
    sql.push_str(mdb.table.as_str());
    if !util_string::is_empty(mdb.where_str.as_str()) {
        sql.push_str(" where ");
        sql.push_str(mdb.where_str.as_str());
    }
    if !util_string::is_empty(mdb.order_by.as_str()) {
        sql.push_str(" order by ");
        sql.push_str(mdb.order_by.as_str());
    }
    sql.push_str(" limit 0, 1");
    sql
}

/// 根据mdb生成 sql 语句(只取一条数据)
fn create_one_sql_by_id_and_for_update(mdb: &Mdb) -> String {
    let mut sql = String::from("SELECT ");
    sql.push_str(mdb.field.as_str());
    sql.push_str(" from ");
    sql.push_str(mdb.table.as_str());
    sql.push_str(" where id = ? FOR UPDATE");
    sql
}

/// 根据mdb生成 count 的 sql 语句
fn create_count_sql_by_mdb(mdb: &Mdb) -> String {
    let mut sql = String::from("SELECT count(*) as cnt from ");
    sql.push_str(mdb.table.as_str());
    if !util_string::is_empty(mdb.where_str.as_str()) {
        sql.push_str(" where ");
        sql.push_str(mdb.where_str.as_str());
    }
    sql
}

/// 根据mdb生成新增语句
fn create_insert_sql_by_mdb(mdb: &Mdb) -> String {
    let mut sql = String::from("insert ");
    sql.push_str(mdb.table.as_str());
    sql.push_str(" set ");
    sql.push_str(mdb.save_str.as_str());
    sql
}

/// 根据mdb生成新增语句
fn create_batch_insert_sql_by_mdb<T: DeserializeOwned + Serialize + Debug>(mdb: &Mdb, datas: &Vec<T>, len: usize) -> Vec<String> {
    let mut sqls: Vec<String> = Vec::new();
    let mut sql_head = String::new();

    let mut s = 0;

    while s < datas.len() {
        let list: Vec<_> = datas.iter().skip(s).take(len).collect();
        s += len;

        let mut body = String::new();
        for bean in list {
            if let Ok((_name, keys, values)) = util_struct::get_struct_info_by_json(bean) {
                if sql_head.is_empty() {
                    // 如果还没有sql语句的前部分
                    sql_head.push_str("insert into ");
                    sql_head.push_str(mdb.table.as_str());
                    sql_head.push_str(" (");

                    let mut field = String::new();
                    for (_, k) in keys.iter().enumerate() {
                        field.push_str(k);
                        field.push_str(",");
                    }

                    sql_head.push_str(&field.trim_matches(&[',']));
                    sql_head.push_str(") values ");
                }

                let mut values_field = String::new();
                for (i, _) in keys.iter().enumerate() {
                    //log::debug!("下标: {}, 字段名: {}", i, k);
                    match &values[i] {
                        Value::Number(v) => {
                            if v.is_f64() {
                                if let Some(f) = v.as_f64() {
                                    values_field.push_str(format!("{},", f).as_str());
                                }
                            } else {
                                if let Some(i) = v.as_i64() {
                                    values_field.push_str(format!("{},", i).as_str());
                                }
                            }
                        }
                        Value::String(v) => {
                            values_field.push_str(format!("'{}',", v).as_str());
                        }
                        _ => (),
                    }
                }
                body.push_str(format!("({}),", values_field.trim_matches(&[','])).as_str());

                //log::debug!("得到更新字段字符串: {}", self.save_str);
            }
        }

        let sql = format!("{}{}", sql_head, body.trim_matches(&[',']));

        //log::info!("\n\n ###22222222222222222222333333333333333#DB##### sql:{}  ### \n\n", sql);

        sqls.push(sql);
    }

    sqls
}

/// 根据mdb生成更新语句
fn create_update_id_sql_by_mdb(mdb: &Mdb) -> String {
    let mut sql = String::from("update ");
    sql.push_str(mdb.table.as_str());
    sql.push_str(" set ");
    sql.push_str(mdb.save_str.as_str());
    sql.push_str(" where id = ?");
    sql
}

/// 根据mdb生成更新语句
#[allow(dead_code)]
fn create_update_sql_by_mdb(mdb: &Mdb) -> String {
    let mut sql = String::from("update ");
    sql.push_str(mdb.table.as_str());
    sql.push_str(" set ");
    sql.push_str(mdb.save_str.as_str());
    sql.push_str(" where ");
    sql.push_str(mdb.where_str.as_str());
    sql
}

/// 根据mdb生成更新语句
fn create_delete_id_sql_by_mdb(mdb: &Mdb) -> String {
    let mut sql = String::from("delete from ");
    sql.push_str(mdb.table.as_str());
    sql.push_str(" where id = ?");
    sql
}

/// 根据mdb生成更新语句
#[allow(dead_code)]
fn create_delete_sql_by_mdb(mdb: &Mdb) -> String {
    let mut sql = String::from("delete from ");
    sql.push_str(mdb.table.as_str());
    sql.push_str(" where ");
    sql.push_str(mdb.where_str.as_str());
    sql
}

/// 获取 old 和 new 改变的地方
#[allow(dead_code)]
fn get_modity(old: &Value, new: &Value) -> String {
    let mut modity_info = String::from("");
    if let Some(o) = old.as_object() {
        if let Some(n) = new.as_object() {
            for o_item in o.iter() {
                let key = o_item.0;
                let o_val = o_item.1;

                //log::debug!(">>>>{} ##### {} => {}", key, o_val, n[key]);

                if !n[key].eq(o_val) {
                    modity_info.push_str(format!("{}:[{:?}=>{:?}],", key, o_val, n[key]).as_str());
                }
            }
        }
    }
    modity_info
}
